﻿--1、生成一个基于求助应答表 Response(Id, Content, AuthorId, ProblemId, CreateTime)的视图VResponse(ResponseId, Content, AuthorId, AuthorName, ProblemId, ProblemTitle, CreateTime)
 --   要求该视图：
 --       能展示应答作者的用户名、应答对应求助的标题和作者用户名
 --       只显示求助悬赏值大于5的数据
 --   演示：在VResponse中插入一条数据，却不能在视图中显示
 --   修改VResponse，让其能避免上述情形

 CREATE  TABLE Response(
 Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 Content NVARCHAR(1000) , 
 AuthorId INT ,
 ProblemId INT , 
 CreateTime dateTIME,
 CONSTRAINT FK_Response_ProblemId FOREIGN KEY(ProblemId) REFERENCES Problem(Id)
ON DELETE CASCADE
 );


 SELECT * FROM Problem;
 SELECT * FROM ContentBase;
 SELECT * FROM [User];
 SELECT * FROM Response;

INSERT INTO Response (Content, AuthorId, ProblemId, CreateTime)
VALUES 
    (N'这个问题我遇到过，解决方案是检查参数格式', 101, 1, '2025-07-01 09:30:00'),
    (N'补充一点：还需要确保网络连接正常', 102, 1, '2025-07-01 10:15:00'),
    (N'建议先查看官方文档的示例代码', 103, 2, '2025-07-02 14:20:00'),
    (N'我尝试过这种方法，确实有效', 101, 3, '2025-07-03 16:45:00');
--VResponse(ResponseId, Content, AuthorId, AuthorName, ProblemId, ProblemTitle, CreateTime)
 --   要求该视图：
 --       能展示应答作者的用户名、应答对应求助的标题和作者用户名
 --       只显示求助悬赏值大于5的数据
go
ALTER VIEW VResponse
AS 
SELECT 
    r.Id AS ResponseId,
    r.Content AS ResponseContent,  -- 应答内容
    r.AuthorId AS ResponseAuthorId,  -- 应答作者ID
    u.UserName AS ResponseAuthorName,  -- 应答作者的用户名（从User表获取）
    r.ProblemId,
    c.Title AS ProblemTitle,  -- 应答对应求助的标题（从ContentBase获取）
    c.Author AS ProblemAuthorName,  -- 应答对应求助的作者用户名（从ContentBase获取）
     p.Reward AS ProblemReward,
    r.CreateTime AS ResponseCreateTime  -- 应答创建时间
    FROM Response r
LEFT JOIN [User] u ON r.AuthorId = u.Id  -- 用LEFT JOIN避免因用户不存在导致整行丢失
LEFT JOIN Problem p ON  r.ProblemId = p.Id
LEFT JOIN ContentBase c ON p.ContentId = c.ContentID
 WHERE p.Reward > 5
 WITH CHECK OPTION;
 GO

SELECT * FROM VResponse ;

INSERT INTO VResponse (
    ResponseContent, 
    ResponseAuthorId, 
    ProblemId, 
    ResponseCreateTime
)
VALUES (
    N'这条应答不会在视图中显示',  -- 应答内容
    4,  -- 应答作者ID（假设[User]表中存在ID=105的用户）
    3,   -- 关联的ProblemID（关键：对应Problem的Reward≤5）
    GETDATE()  -- 创建时间
);

--2、编写存储过程bp_user_register，模拟一起帮用户注册，包含以下逻辑：

--    检查用户名是否重复。如果重复，返回错误代码：1
--    检查用户名密码是否符合“长度不小于4位”的要求。如果不符合，返回错误代码：2
--    如果有邀请人：
--        检查邀请人是否存在，如果不存在，返回错误代码：10
--        检查邀请码是否正确，如果邀请码不正确，返回错误代码：11
--    将用户名、密码和邀请人存入数据库（User）
--    给邀请人增加10个帮帮点积分（帮帮点对应Credit表，可以记录用户的每一次积分获得过程，即：某个用户，在某个时间，因为某某原因，获得若干积分）
--    通知邀请人（Message中生成一条数据）某人使用了他作为邀请人。



--3、创建触发器TR_KeywordUsed，能在关键字变动（求助发布/修改/删除）时重新统计关键字被使用次数Used 
SELECT * FROM Keyword;
SELECT * FROM Problem;
SELECT * FROM ProblemKeywords;

INSERT ProblemKeywords(ProblemID,KeywordID) VALUES(5,10);

GO
CREATE TRIGGER TR_KeywordUsed    
ON  ProblemKeywords
AFTER INSERT   
AS 
BEGIN
    UPDATE Keyword 
    SET Used = (
    --SELECT SUM(KeywordID) FROM ProblemKeywords
     SELECT COUNT(pk.KeywordID)  
        FROM ProblemKeywords pk
        WHERE pk.KeywordID = k.ID  -- 只计算当前ProblemID对应的总和
    )
    -- inserted代表刚插入的数据
    FROM Keyword k
    INNER JOIN inserted i ON k.Id = i.KeywordID ;  -- 关联插入的记录的ProblemID
    --WHERE ProblemID = (SELECT ProblemID FROM inserted);
END
DROP TRIGGER TR_KeywordUsed;
INSERT ProblemKeywords(ProblemID,KeywordID) VALUES(3,15);
INSERT ProblemKeywords(ProblemID,KeywordID) VALUES(2,15);
INSERT ProblemKeywords(ProblemID,KeywordID) VALUES(3,25);


DELETE FROM ProblemKeywords WHERE ProblemID = 8 ;

ALTER TABLE [dbo].[ProblemKeywords]
    ADD CONSTRAINT [FK_ProblemKeywords_Problem] FOREIGN KEY (ProblemID) REFERENCES [dbo].Problem ([Id]) ON DELETE CASCADE;

begin tran
UPDATE Keyword 
    SET Used = ( SELECT COUNT(pk.KeywordID)  
        FROM ProblemKeywords pk WHERE pk.KeywordID = k.ID
        )  FROM Keyword k ; 
commit;
rollback;
